package com.healthcare;
	import java.sql.Connection;
	import java.sql.ResultSet;
	import java.sql.SQLException;
	import java.sql.Statement;
	import java.util.ArrayList;
import java.util.List;

import beans.Articles;
import beans.Posts;
	
	 
	public class DAO {
	    Connection connection;
	    Statement stmt;
	    private int noOfRecords;
	 
	    public DAO() { }
	 
	    private static Connection getConnection()
	            throws SQLException,
	                ClassNotFoundException
	    {
	        Connection con = (Connection) DBConnection.getInstance().getConnection();
	        return con;
	    }
	 
	    public List<Articles> viewArticles(
	                int offset,
	                int noOfRecords, String query)
	                
	               
	    {
	    	  List<Articles> list = new ArrayList<Articles>();
		        Articles articles = null;
	        String queryStatement = "select SQL_CALC_FOUND_ROWS * from aimsfirstconsultdb.articles  where label like '%" + query + "%' or tags like '%" + query + "%' or specialization='"+ query +"'  limit " + offset + ", " + noOfRecords;
	      
	        try {
	            connection = getConnection();
	            stmt = connection.createStatement();
	            ResultSet rs = stmt.executeQuery(queryStatement);
	            System.out.println("Inside DAo articles");
	            while (rs.next()) {
	            	articles = new Articles();
	            articles.setArticleid(rs.getInt(1));
	            articles.setDocid(rs.getInt(2));
	            articles.setLabel(rs.getString(3));
	            articles.setArticle(rs.getString(4));
	            articles.setTags(rs.getString(5));
	            articles.setDate(rs.getString(6));
	            articles.setDoctor(rs.getString(8));
	                list.add(articles);
	            }
	            rs.close();
	 
	            rs = stmt.executeQuery("SELECT FOUND_ROWS()");
	            if(rs.next())
	                this.noOfRecords = rs.getInt(1);
	        } catch (SQLException e) {
	            e.printStackTrace();
	        } catch (ClassNotFoundException e) {
	            e.printStackTrace();
	        }finally
	        {
	            try {
	                if(stmt != null)
	                    stmt.close();
	                if(connection != null)
	                    connection.close();
	                } catch (SQLException e) {
	                e.printStackTrace();
	            }
	        }
	        return list;
	    }
	 
	    public int getNoOfRecords() {
	        return noOfRecords;
	    }
	    public List<Posts> viewPosts(
                int offset,
                int noOfRecords, String query)
                
               
    {
    	  List<Posts> list = new ArrayList<Posts>();
    	  Posts posts = null;
        String queryStatement = "select SQL_CALC_FOUND_ROWS * from aimsfirstconsultdb.posts inner join aimsfirstconsultdb.comments  where label like '%" + query + "%' or posts.quesid = comments.idanswer limit " + offset + ", " + noOfRecords;
      
        try {
            connection = getConnection();
            stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery(queryStatement);
            System.out.println("Inside DAo posts");
            while (rs.next()) {
            	posts = new Posts();
            posts.setQlabel(rs.getString("label"));
            posts.setQuestion(rs.getString("question"));
            posts.setAnswer(rs.getString("answer"));
            posts.setUser(rs.getString("userEmail"));
            posts.setDate(rs.getString("createdDate"));
            posts.setQid(rs.getInt(5));
            System.out.println("Inside DAo posts1");
           list.add(posts);
            }
            rs.close();
 
            rs = stmt.executeQuery("SELECT FOUND_ROWS()");
            if(rs.next())
                this.noOfRecords = rs.getInt(1);
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }finally
        {
            try {
                if(stmt != null)
                    stmt.close();
                if(connection != null)
                    connection.close();
                } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return list;
    }
 
  
}
